package com.mxpio.erp.equipment.service.impl;

import com.mxpio.erp.common.equipment.entity.EquipmentCheckPlan;
import com.mxpio.erp.common.equipment.entity.EquipmentCheckPlanDetail;
import com.mxpio.erp.common.equipment.entity.EquipmentCheckPlanTarget;
import com.mxpio.erp.common.equipment.service.EquipmentCheckPlanService;
import com.mxpio.erp.common.equipment.service.EquipmentReportService;
import com.mxpio.erp.common.equipment.vo.EquipmentReportVO;
import com.mxpioframework.jpa.JpaUtil;
import com.mxpioframework.jpa.query.Criteria;
import com.mxpioframework.jpa.query.Operator;
import com.mxpioframework.system.service.impl.BaseServiceImpl;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.Serializable;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

@Service
public class EquipmentReportServiceImpl implements EquipmentReportService {

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getMaintenanceHours() {
		String sql = "SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 5 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_date_,NOW()),IFNULL(actual_end_date_,NOW()))),0) as value from mb_erp_repair where MONTH ( actual_end_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))\n" +
				" UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 4 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_date_,NOW()),IFNULL(actual_end_date_,NOW()))),0) as value from mb_erp_repair where MONTH ( actual_end_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))\n" +
				"\t UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 3 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_date_,NOW()),IFNULL(actual_end_date_,NOW()))),0) as value from mb_erp_repair where MONTH ( actual_end_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))\n" +
				"\t UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 2 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_date_,NOW()),IFNULL(actual_end_date_,NOW()))),0) as value from mb_erp_repair where MONTH ( actual_end_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))\n" +
				"\t UNION\n" +
				"SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 1 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_date_,NOW()),IFNULL(actual_end_date_,NOW()))),0) as value from mb_erp_repair where MONTH ( actual_end_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))\n" +
				"\t UNION\n" +
				" SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 0 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_date_,NOW()),IFNULL(actual_end_date_,NOW()))),0) as value from mb_erp_repair where MONTH ( actual_end_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))\n" +
				" ";
		List<Object[]> resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertResult(resultList);
	}
	


	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getUpkeepHours() {
		String sql = "SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 5 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_time_,NOW()),IFNULL(actual_end_time_,NOW()))),0) as value from mb_erp_equipment_upkeep_task where MONTH ( actual_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))\n" +
				" UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 4 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_time_,NOW()),IFNULL(actual_end_time_,NOW()))),0) as value from mb_erp_equipment_upkeep_task where MONTH ( actual_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))\n" +
				"\t UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 3 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_time_,NOW()),IFNULL(actual_end_time_,NOW()))),0) as value from mb_erp_equipment_upkeep_task where MONTH ( actual_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))\n" +
				"\t UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 2 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_time_,NOW()),IFNULL(actual_end_time_,NOW()))),0) as value from mb_erp_equipment_upkeep_task where MONTH ( actual_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))\n" +
				"\t UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 1 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_time_,NOW()),IFNULL(actual_end_time_,NOW()))),0) as value from mb_erp_equipment_upkeep_task where MONTH ( actual_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))\n" +
				"\t UNION\n" +
				"  SELECT DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 0 MONTH )), '%Y-%m' ) AS m,IFNULL(SUM(TIMESTAMPDIFF(HOUR,IFNULL(actual_start_time_,NOW()),IFNULL(actual_end_time_,NOW()))),0) as value from mb_erp_equipment_upkeep_task where MONTH ( actual_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))\n" +
				"  ";
		List<Object[]> resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getUpkeepCompletionRate() {
		String sql = "select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 5 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_upkeep_task where id_ in (select id_ from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 4 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_upkeep_task where id_ in (select id_ from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 3 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_upkeep_task where id_ in (select id_ from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 2 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_upkeep_task where id_ in (select id_ from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 1 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_upkeep_task where id_ in (select id_ from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 0 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_upkeep_task where id_ in (select id_ from mb_erp_equipment_upkeep_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))))as mValue)as tt";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertAllResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getCheckCompletionRate() {
		String sql = "select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 5 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_check_task where id_ in (select id_ from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 4 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_check_task where id_ in (select id_ from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 3 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_check_task where id_ in (select id_ from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 2 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_check_task where id_ in (select id_ from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 1 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_check_task where id_ in (select id_ from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 0 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_time_,CURDATE())<=plan_end_time_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_check_task where id_ in (select id_ from mb_erp_equipment_check_task where MONTH ( plan_end_time_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))))as mValue)as tt";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertAllResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getAuditCompletionRate() {
		String sql = "select m, dValue, mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue / dValue END as value from ( select DATE_FORMAT( (DATE_SUB(CURDATE(), INTERVAL 5 MONTH)), '%Y-%m' ) AS m, ( select count(id_) from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 5 MONTH)) ) as dValue,( select sum( CASE IFNULL(audit_end_time_, CURDATE()) <= demand_rect_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_audit where id_ in ( select id_ from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 5 MONTH)) ) ) as mValue ) as tt UNION select m, dValue, mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue / dValue END as value from ( select DATE_FORMAT( (DATE_SUB(CURDATE(), INTERVAL 4 MONTH)), '%Y-%m' ) AS m, ( select count(id_) from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 4 MONTH)) ) as dValue,( select sum( CASE IFNULL(audit_end_time_, CURDATE()) <= demand_rect_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_audit where id_ in ( select id_ from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 4 MONTH)) ) ) as mValue ) as tt UNION select m, dValue, mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue / dValue END as value from ( select DATE_FORMAT( (DATE_SUB(CURDATE(), INTERVAL 3 MONTH)), '%Y-%m' ) AS m, ( select count(id_) from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) ) as dValue,( select sum( CASE IFNULL(audit_end_time_, CURDATE()) <= demand_rect_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_audit where id_ in ( select id_ from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 3 MONTH)) ) ) as mValue ) as tt UNION select m, dValue, mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue / dValue END as value from ( select DATE_FORMAT( (DATE_SUB(CURDATE(), INTERVAL 2 MONTH)), '%Y-%m' ) AS m, ( select count(id_) from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) ) as dValue,( select sum( CASE IFNULL(audit_end_time_, CURDATE()) <= demand_rect_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_audit where id_ in ( select id_ from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 2 MONTH)) ) ) as mValue ) as tt UNION select m, dValue, mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue / dValue END as value from ( select DATE_FORMAT( (DATE_SUB(CURDATE(), INTERVAL 1 MONTH)), '%Y-%m' ) AS m, ( select count(id_) from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) ) as dValue,( select sum( CASE IFNULL(audit_end_time_, CURDATE()) <= demand_rect_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_audit where id_ in ( select id_ from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) ) ) as mValue ) as tt UNION select m, dValue, mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue / dValue END as value from ( select DATE_FORMAT( (DATE_SUB(CURDATE(), INTERVAL 0 MONTH)), '%Y-%m' ) AS m, ( select count(id_) from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 0 MONTH)) ) as dValue,( select sum( CASE IFNULL(audit_end_time_, CURDATE()) <= demand_rect_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_equipment_audit where id_ in ( select id_ from mb_erp_equipment_audit where MONTH (demand_rect_date_) = MONTH (DATE_SUB(CURDATE(), INTERVAL 0 MONTH)) ) ) as mValue ) as tt";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertAllResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getRepairCompletionRate() {
		String sql = "select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 5 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_date_,CURDATE())<=plan_repair_end_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_repair where id_ in (select id_ from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 5 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 4 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_date_,CURDATE())<=plan_repair_end_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_repair where id_ in (select id_ from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 4 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 3 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_date_,CURDATE())<=plan_repair_end_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_repair where id_ in (select id_ from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 3 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 2 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_date_,CURDATE())<=plan_repair_end_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_repair where id_ in (select id_ from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 2 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 1 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_date_,CURDATE())<=plan_repair_end_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_repair where id_ in (select id_ from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 1 MONTH ))))as mValue)as tt UNION select m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE mValue/dValue END as value from ( select DATE_FORMAT( ( DATE_SUB( CURDATE(), INTERVAL 0 MONTH )), '%Y-%m' ) AS m, (select count(id_) from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))) as dValue,(select sum(CASE IFNULL(actual_end_date_,CURDATE())<=plan_repair_end_date_ WHEN true THEN 1 ELSE 0 END ) from mb_erp_repair where id_ in (select id_ from mb_erp_repair where MONTH ( plan_repair_start_date_ ) = MONTH (DATE_SUB( CURDATE(), INTERVAL 0 MONTH ))))as mValue)as tt";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertAllResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getEquipmentFailure() {
		String sql = "select abc_type_ as m,count(id_) as value from mb_erp_repair GROUP BY abc_type_";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getEquipmentStatus() {
		String sql ="select CASE status_ WHEN 0 THEN '购置' WHEN 10 THEN '验收' WHEN 20 THEN '安装'WHEN 30 THEN '使用中'WHEN 40 THEN '维保中'WHEN 50 THEN '停机' WHEN 70 THEN '报废' END as xx ,count(id_) as value from mb_erp_equipment_eqp_basics GROUP BY status_";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getEquipmentNormalStatus() {
		String sql = "SELECT m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE (dValue-mValue)/dValue END as value from (SELECT DATE_FORMAT( ( DATE_SUB( CURDATE( ), INTERVAL 5 MONTH ) ), '%Y-%m' ) AS m,(SELECT count( id_ ) FROM mb_erp_equipment_eqp_basics where install_date_<DATE_SUB( CURDATE( ), INTERVAL 4 MONTH )) as dValue,(SELECT count( DISTINCT eqp_id_ ) FROM mb_erp_equipment_eqp_status_records WHERE MONTH ( create_time ) = MONTH ( DATE_SUB( CURDATE( ), INTERVAL 5 MONTH ) ) AND now_status_ = '40' ) as mValue) as tt UNION SELECT m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE (dValue-mValue)/dValue END from (SELECT DATE_FORMAT( ( DATE_SUB( CURDATE( ), INTERVAL 4 MONTH ) ), '%Y-%m' ) AS m,(SELECT count( id_ ) FROM mb_erp_equipment_eqp_basics where install_date_<DATE_SUB( CURDATE( ), INTERVAL 3 MONTH )) as dValue,(SELECT count( DISTINCT eqp_id_ ) FROM mb_erp_equipment_eqp_status_records WHERE MONTH ( create_time ) = MONTH ( DATE_SUB( CURDATE( ), INTERVAL 4 MONTH ) ) AND now_status_ = '40' ) as mValue) as tt UNION SELECT m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE (dValue-mValue)/dValue END from (SELECT DATE_FORMAT( ( DATE_SUB( CURDATE( ), INTERVAL 3 MONTH ) ), '%Y-%m' ) AS m,(SELECT count( id_ ) FROM mb_erp_equipment_eqp_basics where install_date_< DATE_SUB( CURDATE( ), INTERVAL 2 MONTH ) ) as dValue,(SELECT count( DISTINCT eqp_id_ ) FROM mb_erp_equipment_eqp_status_records WHERE MONTH ( create_time ) = MONTH ( DATE_SUB( CURDATE( ), INTERVAL 3 MONTH ) ) AND now_status_ = '40' ) as mValue) as tt UNION SELECT m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE (dValue-mValue)/dValue END from (SELECT DATE_FORMAT( ( DATE_SUB( CURDATE( ), INTERVAL 2 MONTH ) ), '%Y-%m' ) AS m,(SELECT count( id_ ) FROM mb_erp_equipment_eqp_basics where install_date_< DATE_SUB( CURDATE( ), INTERVAL 1 MONTH ) ) as dValue,(SELECT count( DISTINCT eqp_id_ ) FROM mb_erp_equipment_eqp_status_records WHERE MONTH ( create_time ) = MONTH ( DATE_SUB( CURDATE( ), INTERVAL 2 MONTH ) ) AND now_status_ = '40' ) as mValue) as tt UNION SELECT m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE (dValue-mValue)/dValue END from (SELECT DATE_FORMAT( ( DATE_SUB( CURDATE( ), INTERVAL 1 MONTH ) ), '%Y-%m' ) AS m,(SELECT count( id_ ) FROM mb_erp_equipment_eqp_basics where install_date_< DATE_SUB( CURDATE( ), INTERVAL 0 MONTH ) ) as dValue,(SELECT count( DISTINCT eqp_id_ ) FROM mb_erp_equipment_eqp_status_records WHERE MONTH ( create_time ) = MONTH ( DATE_SUB( CURDATE( ), INTERVAL 1 MONTH ) ) AND now_status_ = '40' ) as mValue) as tt UNION SELECT m,dValue,mValue,CASE dValue WHEN 0 THEN 1 ELSE (dValue-mValue)/dValue END from (SELECT DATE_FORMAT( ( DATE_SUB( CURDATE( ), INTERVAL 0 MONTH ) ), '%Y-%m' ) AS m,(SELECT count( id_ ) FROM mb_erp_equipment_eqp_basics where install_date_< DATE_SUB( CURDATE( ), INTERVAL -1 MONTH ) ) as dValue,(SELECT count( DISTINCT eqp_id_ ) FROM mb_erp_equipment_eqp_status_records WHERE MONTH ( create_time ) = MONTH ( DATE_SUB( CURDATE( ), INTERVAL 0 MONTH ) ) AND now_status_ = '40' ) as mValue) as tt";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertAllResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getEquipmentFailureCount() {
		String sql = "SELECT eb.eqp_code_, eb.eqp_name_, count(er.id_) as value, SUM(TIMESTAMPDIFF(MINUTE,er.create_time,IFNULL(er.next_operate_time_,NOW()))) as timeValue FROM mb_erp_equipment_eqp_status_records er LEFT JOIN mb_erp_equipment_eqp_basics eb ON er.eqp_id_ = eb.id_ WHERE er.now_status_ = 40 GROUP BY er.eqp_id_";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertEqpResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getMeanTimeBeforeFailure() {
		String sql = "SELECT eb.eqp_code_, eb.eqp_name_, count(er.id_) as value, (SUM(TIMESTAMPDIFF(MINUTE,er.create_time,IFNULL(er.next_operate_time_,NOW()))) / count( er.id_ ) ) as timeValue FROM mb_erp_equipment_eqp_status_records er LEFT JOIN mb_erp_equipment_eqp_basics eb ON er.eqp_id_ = eb.id_ WHERE er.now_status_ = 30 GROUP BY er.eqp_id_";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertEqpResult(resultList);
	}

	@Override
	@Transactional(readOnly = true)
	public List<EquipmentReportVO> getMeanTimeToRepair() {
		String sql = "SELECT eb.eqp_code_, eb.eqp_name_, count(er.id_) as value, (SUM(TIMESTAMPDIFF(MINUTE,er.create_time,IFNULL(er.next_operate_time_,NOW()))) / count( er.id_ ) ) as timeValue FROM mb_erp_equipment_eqp_status_records er LEFT JOIN mb_erp_equipment_eqp_basics eb ON er.eqp_id_ = eb.id_ WHERE er.now_status_ = 40 GROUP BY er.eqp_id_";
		List resultList = JpaUtil.nativeQuery(sql).getResultList();
		return convertEqpResult(resultList);
	}

	private List<EquipmentReportVO> convertResult(List<Object[]> resultList){
		ArrayList<EquipmentReportVO> result = new ArrayList<>();
		for (Object[] row : resultList){
			EquipmentReportVO report = new EquipmentReportVO();
			if (row[0] != null){
				report.setM((String) row[0]);
			}
			Object value = row[1];
			if (value != null){
				if(value instanceof BigInteger){
					BigInteger bigValue = (BigInteger) row[1];
					report.setLValue(bigValue.longValue());
				}else{
					report.setValue((BigDecimal) value);
				}
			}

			result.add(report);
		}
		return result;
	}

	private List<EquipmentReportVO> convertAllResult(List<Object[]> resultList){
		ArrayList<EquipmentReportVO> result = new ArrayList<>();
		for (Object[] row : resultList){
			EquipmentReportVO report = new EquipmentReportVO();
			report.setM((String) row[0]);
			if (row[1] != null){
				BigInteger value = (BigInteger) row[1];
				report.setLValue(value.longValue());
			}
			report.setDValue((BigDecimal) row[2]);
			report.setValue((BigDecimal) row[3]);
			result.add(report);
		}
		return result;
	}
	private List<EquipmentReportVO> convertEqpResult(List<Object[]> resultList){
		ArrayList<EquipmentReportVO> result = new ArrayList<>();
		for (Object[] row : resultList){
			EquipmentReportVO report = new EquipmentReportVO();
			if (row[0] != null){
				report.setEqpCode((String) row[0]);
			}
			if (row[1] != null){
				report.setEqpName((String) row[1]);
			}
			if (row[2] != null){
				BigInteger value = (BigInteger) row[2];
				report.setLValue(value.longValue());
			}
			if (row[3] != null){
				report.setValue((BigDecimal) row[3]);
			}
			result.add(report);
		}
		return result;
	}
}
